package com.iot.manage.dao;

import com.alibaba.fastjson.JSONObject;
import com.iot.manage.pojo.Device;
import com.iot.manage.pojo.SysCode;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface DeviceMapper {

    @Select("<script>" +
            "SELECT\n" +
            "\tdeviceId,\n" +
            "\tdeviceCode,\n" +
            "\tdeviceName,\n" +
            "\tdtypeCode,\n" +
            "\tmodelCode,\n" +
            "\tcompanyCode,\n" +
            "\tgatewayCode,\n" +
            "\tdeviceStatus,\n" +
            "\tdeviceArg,\n" +
            "\tparentCode\n" +
            "FROM ( \n" +
            "SELECT\n" +
            "\tROWNUM AS rowno,\n" +
            "\tt.DEVICE_ID deviceId,\n" +
            "\tt.DEVICE_CODE deviceCode,\n" +
            "\tt.DEVICE_NAME deviceName,\n" +
            "\ta.CODE_NAME dtypeCode,\n" +
            "\tb.MODEL_NAME modelCode,\n" +
            "\tc.COMPANY_NAME companyCode,\n" +
            "\td.GATEWAY_NAME gatewayCode,\n" +
            "\te.CODE_NAME deviceStatus,\n" +
            "\tt.DEVICE_ARG deviceArg,\n" +
            "\tt.PARENT_CODE parentCode\n" +
            "FROM T_DEVICE t \n" +
            "LEFT JOIN (SELECT * FROM T_SYS_CODE WHERE CODE_TYPE='设备类型') a ON t.DTYPE_CODE = a.CODE_VALUE\n" +
            "LEFT JOIN T_DEVICE_MODEL b ON t.MODEL_CODE = b.MODEL_CODE\n" +
            "LEFT JOIN T_DEVICE_COMPANY c ON t.COMPANY_CODE = c.COMPANY_CODE\n" +
            "LEFT JOIN T_GATEWAY d ON t.GATEWAY_CODE = d.GATEWAY_CODE\n" +
            "LEFT JOIN (SELECT * FROM T_SYS_CODE WHERE CODE_TYPE='设备状态') e ON t.DEVICE_STATUS = e.CODE_VALUE \n" +
            "WHERE (t.DEVICE_NAME LIKE CONCAT(CONCAT('%', #{query}),'%') OR t.DEVICE_CODE LIKE CONCAT(CONCAT('%', #{query}),'%') OR t.PARENT_CODE LIKE CONCAT(CONCAT('%', #{query}),'%')) AND ROWNUM &lt;= #{pagesize}\n" +
            "ORDER BY t.DEVICE_ID"+
            ") temp\n" +
            "WHERE temp.rowno &gt; #{start}" +
            "</script>")
    List<Device> findDevicesList(@Param("query") String query, @Param("start") int start, @Param("pagesize") int pagesize);

    @Select("<script>SELECT COUNT(a.DEVICE_ID) FROM T_DEVICE a WHERE a.DEVICE_NAME LIKE CONCAT(CONCAT('%', #{query}),'%') OR a.DEVICE_CODE LIKE CONCAT(CONCAT('%', #{query}),'%') OR a.PARENT_CODE LIKE CONCAT(CONCAT('%', #{query}),'%')</script>")
    int countDevicesList(String query);

    @Select("<script>SELECT a.CODE_VALUE codeValue, a.CODE_NAME codeName FROM T_SYS_CODE a WHERE a.CODE_TYPE='设备类型' ORDER BY a.CODE_VALUE</script>")
    List<SysCode> findDtypeCodeList();

    @Select("<script>SELECT a.CODE_VALUE codeValue, a.CODE_NAME codeName FROM T_SYS_CODE a WHERE a.CODE_TYPE='设备状态' ORDER BY a.CODE_VALUE</script>")
    List<SysCode> findDeviceStatusList();

    @Select("<script>SELECT a.MODEL_CODE codeValue, a.MODEL_NAME codeName FROM T_DEVICE_MODEL a  ORDER BY a.MODEL_ID</script>")
    List<SysCode> findModelCodeList();

    @Select("<script>SELECT a.COMPANY_CODE codeValue, a.COMPANY_NAME codeName FROM T_DEVICE_COMPANY a  ORDER BY a.COMPANY_ID</script>")
    List<SysCode> findCompanyCodeList();

    @Select("<script>SELECT a.GATEWAY_CODE codeValue, a.GATEWAY_NAME codeName FROM T_GATEWAY a WHERE a.GATEWAY_TYPE = 'gtype-agent' ORDER BY a.GATEWAY_ID</script>")
    List<SysCode> findGatewayCodeList();

    @Select("<script>SELECT a.DEVICE_CODE codeValue, a.DEVICE_CODE codeName FROM T_DEVICE a  ORDER BY a.DEVICE_ID</script>")
    List<SysCode> findParentCodeList();

    @Insert("<script>" +
            "INSERT INTO T_DEVICE \n" +
            "(DEVICE_ID,DEVICE_CODE,DEVICE_NAME,DTYPE_CODE,MODEL_CODE,COMPANY_CODE,GATEWAY_CODE,DEVICE_STATUS" +
            "<if test=\" deviceArg != null and deviceArg != '' \">"+
            ",DEVICE_ARG" +
            "</if>"+
            "<if test=\" parentCode != null and parentCode != '' \">"+
            ",PARENT_CODE" +
            "</if>"+
            ") \n" +
            "VALUES \n" +
            "(#{deviceId},#{deviceCode},#{deviceName},#{dtypeCode},#{modelCode},#{companyCode},#{gatewayCode},#{deviceStatus}" +
            "<if test=\" deviceArg != null and deviceArg != '' \">"+
            ",#{deviceArg}" +
            "</if>"+
            "<if test=\" parentCode != null and parentCode != '' \">"+
            ",#{parentCode}" +
            "</if>"+
            ")" +
            "</script>")
    void addDeviceInfo(JSONObject params);

    @Select("<script>" +
            "SELECT\n" +
            "\tt.DEVICE_ID deviceId,\n" +
            "\tt.DEVICE_CODE deviceCode,\n" +
            "\tt.DEVICE_NAME deviceName,\n" +
            "\ta.CODE_VALUE dtypeCode,\n" +
            "\tb.MODEL_CODE modelCode,\n" +
            "\tc.COMPANY_CODE companyCode,\n" +
            "\td.GATEWAY_CODE gatewayCode,\n" +
            "\te.CODE_VALUE deviceStatus,\n" +
            "\tt.DEVICE_ARG deviceArg,\n" +
            "\tNVL(t.PARENT_CODE,' ') parentCode\n" +
            "FROM T_DEVICE t \n" +
            "LEFT JOIN (SELECT * FROM T_SYS_CODE WHERE CODE_TYPE='设备类型') a ON t.DTYPE_CODE = a.CODE_VALUE\n" +
            "LEFT JOIN T_DEVICE_MODEL b ON t.MODEL_CODE = b.MODEL_CODE\n" +
            "LEFT JOIN T_DEVICE_COMPANY c ON t.COMPANY_CODE = c.COMPANY_CODE\n" +
            "LEFT JOIN T_GATEWAY d ON t.GATEWAY_CODE = d.GATEWAY_CODE\n" +
            "LEFT JOIN (SELECT * FROM T_SYS_CODE WHERE CODE_TYPE='设备状态') e ON t.DEVICE_STATUS = e.CODE_VALUE \n" +
            "WHERE t.DEVICE_ID = #{params} OR t.DEVICE_CODE = #{params}" +
            "</script>")
    Device findDeviceInfoById(String params);

    @Update("<script>" +
            "UPDATE T_DEVICE SET \n" +
            "\tDEVICE_CODE = #{deviceCode},\n" +
            "\tDEVICE_NAME = #{deviceName},\n" +
            "\tDTYPE_CODE = #{dtypeCode},\n" +
            "\tMODEL_CODE = #{modelCode},\n" +
            "\tCOMPANY_CODE = #{companyCode},\n" +
            "\tGATEWAY_CODE = #{gatewayCode},\n" +
            "\tDEVICE_STATUS = #{deviceStatus}\n" +
            "<if test=\" deviceArg != null\">"+
            "\t,DEVICE_ARG = #{deviceArg}\n" +
            "</if>"+
            "<if test=\" parentCode != null\">"+
            "\t,PARENT_CODE = #{parentCode}\n" +
            "</if>"+
            "WHERE DEVICE_ID = #{deviceId}" +
            "</script>")
    void updateDeviceInfo(JSONObject params);

    @Delete("<script>DELETE FROM T_DEVICE WHERE DEVICE_ID = #{deviceId}</script>")
    void removeDeviceInfo(String deviceId);

    @Update("<script>UPDATE T_DEVICE SET PARENT_CODE = #{deviceCode} WHERE PARENT_CODE = (SELECT DEVICE_CODE FROM T_DEVICE WHERE DEVICE_ID = #{deviceId})</script>")
    void updateDeviceParentCodeInfo(JSONObject params);

    @Select("<script>SELECT count(1) FROM T_DEVICE WHERE PARENT_CODE = #{deviceCode}</script>")
    int countDeviceParentByCode(String deviceCode);

    @Update("<script>UPDATE T_DEVICE SET PARENT_CODE = '' WHERE PARENT_CODE = #{deviceCode}</script>")
    void updateDeviceInfoParent(String deviceCode);

    @Delete("<script>DELETE FROM T_AREA_DEVICE WHERE DEVICE_ID = #{deviceId}</script>")
    void deleteRelevancyAreaById(String deviceId);
}
